package JDBC;

import org.junit.Test;

import java.sql.*;

/**
 * @Fou Jack of all trades and master of none.
 * @date 2022/1/6 10:10
 */
public class DAOBatch {
    //1、使用Statement进行批处理
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        try{
            //注册驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //通过驱动获取连接
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/daodemodb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","root");
            stmt = conn.createStatement();
            for (int i=0;i<2;i++){
                String sql = "insert into t_user (name,age,money) values ('StatementTest"+i+"',"+25+i+",432.23)";
                stmt.addBatch(sql);
            }
            //批处理
            int[] result = stmt.executeBatch();
            System.out.println("影响的行数为：");
            for (int i=0;i<result.length;i++){
                System.out.println(result[i]+" ");
            }
        }catch (SQLException e){
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        finally {
            //释放资源
            if (conn!=null){
                conn.close();
            }
            if (stmt!=null){
                stmt.close();
            }
            if (rs!=null){
                rs.close();
            }
        }
    }

    //2、使用PreparedStatement进行批处理
    @Test
    public void Batch2() throws SQLException{
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement prestmt = null;
        try{
            //注册驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //通过注册驱动获取连接对象
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/daodemodb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","root");
            String sql = "insert into t_user (name ,age,money) values(?,?,?)";
            prestmt = conn.prepareStatement(sql);
            for (int i=0;i<2;i++){
                prestmt.setString(1,"PreparedStatementTest"+i);
                prestmt.setInt(2,25+i);
                prestmt.setDouble(3,333.33);
                prestmt.addBatch();
            }
            //批处理
            int [] result = prestmt.executeBatch();
            System.out.println("影响的行数分别为：");
            for (int i=0;i<result.length;i++){
                System.out.println(result[i]+" ");
            }

        }catch (SQLException e){
            System.out.println(e.getMessage());
            e.printStackTrace();
        }finally {
            //释放资源
            if (conn!=null){
                conn.close();
            }
            if (rs!=null){
                rs.close();
            }
            if (prestmt!=null){
                prestmt.close();
            }
        }


    }

}
